package com.szholly.pro.watergas_server.ps.service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import com.szholly.pro.watergas_server.common.data.DataUtil;
import com.szholly.data.general.GlobalParameter;
import com.szholly.data.general.RowBase;
import com.szholly.plug.form.excel.utils.StringUtil;

@Service
public class QCSampleManagementService {
	protected Logger logger = LoggerFactory.getLogger(getClass());
	
	/**
	 * 获取采样信息
	 * @param resq
	 * @param res
	 * @throws Exception
	 */
	public List<RowBase> getSampleDataList(Map<String, String> parm) throws Exception{
		String sql="select t2.samplenumber, (case count(distinct(t1.samplenumber||t1.monitorparam)) when 0 then 1 else count(distinct(t1.samplenumber||t1.monitorparam)) end)as detailCount,t3.companyname,"
				+" t4.s_domaincaption as qcname, t5.s_domaincaption as monitorName,t2.datatime, wmsys.wm_concat(distinct t8.personname) as personname,"
				+" t2.samplevideo,t2.samplephoto,t2.ispxy from QC_SAMPLEDATA t2  left join qc_sampledetaildata t1 on t1.samplenumber=t2.samplenumber"
				+" left join qc_companyinfo t3 on t3.companycode=t2.company left join DATA_DEFULATDOMAIN t4 on t4.s_domaincode=t2.qctype and t4.s_domainname = '质控类型' "
				+" left join DATA_DEFULATDOMAIN t5 on t5.s_domaincode=t2.monitortype and t5.s_domainname = '质控监测类型' "
				+" left join qc_samplenumber_person t6 on t6.samplenumber = t2.samplenumber"
				+" left join qc_COMPANYPERSONINFO t8 on t8.personcode = t6.samplepersoncode   and t8.persontype = '1' where 1 = 1  and t1.type='0'";
		if(StringUtil.IsNotNullOrEmpty(parm.get("MONITORINGTYPE"))){//监测类型		
			sql +=" and t2.monitortype in("+ParamPartition(parm.get("MONITORINGTYPE"))+")  ";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("MonitorMethod"))){//监测手段
			sql +=" and t3.companytype='"+parm.get("MonitorMethod")+"'";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("CompanyCode"))){//公司分类
			sql +=" and t2.company in("+ParamPartition(parm.get("CompanyCode"))+")";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("QCType"))){//质控类型
			sql +=" and  t2.qctype in("+ParamPartition(parm.get("QCType"))+")";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("SAMPLENUMBER"))){//样品编号
			sql +=" and  t2.samplenumber ='"+parm.get("SAMPLENUMBER")+"'";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("startDate"))){
			sql+=" and t2.datatime>=to_date('"+parm.get("startDate")+" 00:00:00','yyyy-mm-dd hh24:mi:ss')";			
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("endDate"))){
			sql+=" and t2.datatime<=to_date('"+parm.get("endDate")+" 23:59:59','yyyy-mm-dd hh24:mi:ss')";			
		}
		sql+="   group by t2.samplenumber,t3.companyname,t4.s_domaincaption,t5.s_domaincaption,t2.datatime,t2.samplevideo,t2.samplephoto,t2.ispxy "
				+" order by t5.s_domaincaption,t3.companyname,t4.s_domaincaption,t2.samplenumber,t2.datatime desc";
		
		List<GlobalParameter> listParam = new ArrayList<>();
		List<RowBase> SampleList = DataUtil.getSelectRows(sql, listParam);
		return SampleList;
		
	}
	/**
	 * 获取采样详情信息
	 * @param resq
	 * @param res
	 * @throws Exception
	 */
	public List<RowBase> getSampleDetailList(Map<String, String> parm) throws Exception{		
		String sqldetail="select t1.samplenumber,t7.monitorparamname,t1.monitorvalue from QC_SAMPLEDATA t2 "
				+" left join  qc_sampledetaildata t1 on t1.samplenumber=t2.samplenumber "
				+" left join qc_companyinfo t3 on t3.companycode=t2.company "
				+" left join DATA_DEFULATDOMAIN t4 on t4.s_domaincode=t2.qctype and t4.s_domainname = '质控类型'"
				+" left join DATA_DEFULATDOMAIN t5 on t5.s_domaincode=t2.monitortype and t5.s_domainname = '质控监测类型'"
				+" left join (select t.*,t1.companycode from qc_monitorparam t left join qc_companyparaminfo t1"
				+" on t.guid = t1.paramguid and t.isfieldparam='0') t7 on t7.monitorparamcode=t1.MONITORPARAM and t7.monitortypecode=t2.MONITORTYPE"
				+" and t7.companycode=t2.COMPANY where 1 = 1  and t1.type='0'";
		if(StringUtil.IsNotNullOrEmpty(parm.get("MONITORINGTYPE"))){//监测类型					
			sqldetail +=" and t2.monitortype in("+ParamPartition(parm.get("MONITORINGTYPE"))+")  ";
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("MonitorMethod"))){//监测手段			
			sqldetail +=" and t3.companytype='"+parm.get("MonitorMethod")+"'";
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("CompanyCode"))){//公司分类			
			sqldetail +=" and t2.company in("+ParamPartition(parm.get("CompanyCode"))+")";
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("QCType"))){//质控类型			
			sqldetail +=" and  t2.qctype in("+ParamPartition(parm.get("QCType"))+")";
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("SAMPLENUMBER"))){//样品编号			
			sqldetail +=" and  t2.samplenumber ='"+parm.get("SAMPLENUMBER")+"'";
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("startDate"))){			
			sqldetail+=" and t2.datatime>=to_date('"+parm.get("startDate")+" 00:00:00','yyyy-mm-dd hh24:mi:ss')";
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("endDate"))){			
			sqldetail+=" and t2.datatime<=to_date('"+parm.get("endDate")+" 23:59:59','yyyy-mm-dd hh24:mi:ss')";
		}		
		sqldetail+=" order by t5.s_domaincaption,t3.companyname,t4.s_domaincaption,t1.samplenumber,t2.datatime asc ";
		List<GlobalParameter> listParam = new ArrayList<>();
		List<RowBase> SampleDetailList = DataUtil.getSelectRows(sqldetail, listParam);
		return SampleDetailList;
		
	}
	/**
	 * 获取采样人
	 * @param resq
	 * @param res
	 * @throws Exception
	 */
	public List<RowBase> getSamplePersonList(Map<String, String> parm) throws Exception{		
		String sqlPerson="select t1.samplenumber,wmsys.wm_concat(distinct t8.personname) as personname"
				+" from QC_SAMPLEDATA t2 "
				+" left join qc_sampledetaildata t1 on t1.samplenumber=t2.samplenumber left join qc_companyinfo t3 on t3.companycode=t2.company "
				+" left join DATA_DEFULATDOMAIN t4 on t4.s_domaincode=t2.qctype and t4.s_domainname = '质控类型'"
				+" left join DATA_DEFULATDOMAIN t5 on t5.s_domaincode=t2.monitortype and t5.s_domainname = '质控监测类型' "
				+" left join qc_samplenumber_person t6 on t6.samplenumber=t2.samplenumber  "
				+" left join qc_COMPANYPERSONINFO t8 on t8.personcode=t6.samplepersoncode where 1 = 1 ";
		if(StringUtil.IsNotNullOrEmpty(parm.get("MONITORINGTYPE"))){//监测类型					
			sqlPerson +=" and t2.monitortype in("+ParamPartition(parm.get("MONITORINGTYPE"))+")  ";
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("MonitorMethod"))){//监测手段			
			sqlPerson +=" and t3.companytype='"+parm.get("MonitorMethod")+"'";
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("CompanyCode"))){//公司分类			
			sqlPerson +=" and t2.company in("+ParamPartition(parm.get("CompanyCode"))+")";
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("QCType"))){//质控类型			
			sqlPerson +=" and  t2.qctype in("+ParamPartition(parm.get("QCType"))+")";
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("SAMPLENUMBER"))){//样品编号			
			sqlPerson +=" and  t2.samplenumber ='"+parm.get("SAMPLENUMBER")+"'";
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("startDate"))){			
			sqlPerson+=" and t2.datatime>=to_date('"+parm.get("startDate")+" 00:00:00','yyyy-mm-dd hh24:mi:ss')";
		}
		if(StringUtil.IsNotNullOrEmpty(parm.get("endDate"))){			
			sqlPerson+=" and t2.datatime<=to_date('"+parm.get("endDate")+" 23:59:59','yyyy-mm-dd hh24:mi:ss')";
		}		
		sqlPerson+="  group by t1.samplenumber,t5.s_domaincaption,t3.companyname,t4.s_domaincaption,t2.datatime"
				+" order by t5.s_domaincaption,t3.companyname,t4.s_domaincaption,t1.samplenumber,t2.datatime asc";
		List<GlobalParameter> listParam = new ArrayList<>();
		List<RowBase> SamplePersonList = DataUtil.getSelectRows(sqlPerson, listParam);
		return SamplePersonList;
		
	}
	
	public String ParamPartition(String paramData){
		String[] param=paramData.split(",");
		String StrParam="";
		for(int i=0;i<param.length;i++){
			if(param.length-i==1){
				StrParam+="'"+param[i]+"'";
			}else{
				StrParam+="'"+param[i]+"',";
			}			
		}
		return StrParam;
	}
}
